﻿using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace MLSMooc.Helper
{
    public class SalesSysSqlHelper
	{
		public static readonly string connectionString;

		static SalesSysSqlHelper()
		{
			connectionString = ConfigurationManager.ConnectionStrings["SalesSysConnectionString"].ConnectionString;
		}

		public static DataSet SQLExecuteDataset(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
		{
			using (SqlConnection conn = new SqlConnection(connectionString))
			{
				SqlCommand command = new SqlCommand(cmdText, conn);
				command.CommandTimeout = 6000000;
				command.CommandType = cmdType;

				if (commandParameters != null)
				{
					foreach (SqlParameter parm in commandParameters)
						command.Parameters.Add(parm);
				}
				SqlDataAdapter adapter = new SqlDataAdapter(command);
				DataSet ds = new DataSet();
				adapter.Fill(ds);
				return ds;
			}
		}

		public static DataSet SQLExecuteDataset(string connString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
		{
			using (SqlConnection conn = new SqlConnection(connString))
			{
				SqlCommand command = new SqlCommand(cmdText, conn);
				command.CommandTimeout = 6000000;
				command.CommandType = cmdType;

				if (commandParameters != null)
				{
					foreach (SqlParameter parm in commandParameters)
						command.Parameters.Add(parm);
				}
				SqlDataAdapter adapter = new SqlDataAdapter(command);
				DataSet ds = new DataSet();
				adapter.Fill(ds);
				return ds;
			}
		}

		public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
		{

			SqlCommand cmd = new SqlCommand();
			cmd.CommandTimeout = 6000000;
			using (SqlConnection conn = new SqlConnection(connectionString))
			{
				PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
				int val = cmd.ExecuteNonQuery();
				cmd.Parameters.Clear();
				return val;
			}
		}

		public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
		{

			SqlCommand cmd = new SqlCommand();
			cmd.CommandTimeout = 6000000;
			PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}

		public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandTimeout = 6000000;
			PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}

		public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandTimeout = 6000000;
			SqlConnection conn = new SqlConnection(connectionString);

			try
			{
				PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
				SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

				cmd.Parameters.Clear();
				return rdr;
			}
			catch
			{
				conn.Close();
				throw;
			}
		}

		public static SqlDataReader ExecuteReader(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandTimeout = 6000000;
			try
			{
				PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
				SqlDataReader rdr = cmd.ExecuteReader();

				cmd.Parameters.Clear();
				return rdr;
			}
			catch
			{
				conn.Close();
				throw;
			}
		}

		public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandTimeout = 6000000;
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
				object val = cmd.ExecuteScalar();
				cmd.Parameters.Clear();
				return val;
			}
		}

		public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandTimeout = 6000000;
			PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
			object val = cmd.ExecuteScalar();
			cmd.Parameters.Clear();
			return val;

		}

		public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandTimeout = 6000000;
			PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
			object val = cmd.ExecuteScalar();
			cmd.Parameters.Clear();
			return val;
		}

		private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
		{

			if (conn.State != ConnectionState.Open)
				conn.Open();

			cmd.Connection = conn;
			cmd.CommandText = cmdText;
			cmd.CommandTimeout = 6000000;
			if (trans != null)
				cmd.Transaction = trans;

			cmd.CommandType = cmdType;

			if (cmdParms != null)
			{
				foreach (SqlParameter parm in cmdParms)
					cmd.Parameters.Add(parm);
			}
		}
	}
}
